package com.baturu.ford.carvin.dal;

import com.baturu.offlineFord.dto.PartDetailDTO;
import com.baturu.vin.ford.dto.FordV2VinAnalyserResultDTO;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * @Author: chenjiahao
 * @Time: 2019/1/17
 */
public interface DealVinDAO {

    @Select({"SELECT max(ID) FROM tmp_new_vin"})
    Integer queryMaxId();

    @Select({"SELECT t.id ,vinCode, date, carType,ifnull(t10.f2,t.carBody) carBody,ifnull(t7.f2,t.ver) ver, ifnull(t3.f2,t.engine) engine, " +
            "ifnull(t4.f2,t.transmission) transmission, ifnull(t12.f2,t.driven) driven,ifnull(t9.f2,t.carradio) carradio, " +
            "ifnull(t6.f2,t.displacement) displacement,ifnull(t8.f2,t.airc) airc,ifnull(t5.f2,t.area) area, " +
            "ifnull(t11.f2,t.carOilColor) carOilColor, ifnull(t1.f2,t.enviroment) enviroment, ifnull(t2.f2,t.trim) trim \n" +
            "FROM tmp_new_vin t\n" +
            "left join ford_epc_enviroment t1 on t1.f1 = t.enviroment\n" +
            "left join ford_epc_trim t2 on t2.f1 = t.trim\n" +
            "left join ford_epc_engine t3 on t3.f1 = t.`engine`\n" +
            "left join ford_epc_transmission t4 on t4.f1 = t.transmission\n" +
            "left join ford_epc_area t5 on t5.f1 = t.area\n" +
            "left join ford_epc_displacement t6 on t6.f1 = t.displacement\n" +
            "left join ford_epc_version t7 on t7.f1 = t.ver\n" +
            "left join ford_epc_airc t8 on t8.f1 = t.airc\n" +
            "left join ford_epc_carradio t9 on t9.f1 = t.carradio\n" +
            "left join ford_epc_car_body t10 on t10.f1 = t.carBody\n" +
            "left join ford_epc_car_oil_color t11 on t11.f1 = t.carOilColor\n" +
            "left join ford_epc_driven t12 on t12.f1 = t.driven",
            "WHERE t.id = #{id}",})
    FordV2VinAnalyserResultDTO queryV2ById(@Param("id") Integer vinCode);

//    @Update({"UPDATE ford_epc_vin\n" +
//            " set carBody = #{dto.carBody}\n" +
//            "  ,ver = #{dto.ver}\n" +
//            "  ,engine = #{dto.engine}\n" +
//            "  ,transmission = #{dto.transmission}\n" +
//            "  ,driven = #{dto.driven}\n" +
//            "  ,carradio = #{dto.carradio}\n" +
//            "  ,displacement = #{dto.displacement}\n" +
//            "  ,airc = #{dto.airc}\n" +
//            "  ,area = #{dto.area}\n" +
//            "  ,carOilColor = #{dto.carOilColor}\n" +
//            "  ,enviroment = #{dto.enviroment}\n" +
//            "  ,trim = #{dto.trim}\n" +
//            " ,date=#{dto.date}",
//            "where id = #{id}"})
//    void updateVin(@Param("dto") FordV2VinAnalyserResultDTO dto, @Param("id") Integer id);

    @Insert({"<script>INSERT INTO tmp_new_vin (id,vinCode, date, carType, carBody, ver, engine, transmission, driven, carradio, displacement, airc, area, carOilColor, enviroment, trim) VALUES ",
            "<foreach item='dto' collection='dtos' open ='(' separator='),(' close = ')'>",
            "#{dto.id},\n" +
            "#{dto.vinCode},\n" +
                    "#{dto.date},\n" +
                    "#{dto.carType},\n" +
                    "#{dto.carBody},\n" +
                    "#{dto.ver},\n" +
                    "#{dto.engine},\n" +
                    "#{dto.transmission},\n" +
                    "#{dto.driven},\n" +
                    "#{dto.carradio},\n" +
                    "#{dto.displacement},\n" +
                    "#{dto.airc},\n" +
                    "#{dto.area},\n" +
                    "#{dto.carOilColor},\n" +
                    "#{dto.enviroment},\n" +
                    "#{dto.trim}\n" +
            "</foreach>",
            "on duplicate key update " +
                    "date = VALUES(date),\n" +
                    "carType = VALUES(carType),\n" +
                    "carBody = VALUES(carBody),\n" +
                    "ver = VALUES(ver),\n" +
                    "engine = VALUES(engine),\n" +
                    "transmission = VALUES(transmission),\n" +
                    "driven = VALUES(driven),\n" +
                    "carradio = VALUES(carradio),\n" +
                    "displacement = VALUES(displacement),\n" +
                    "airc = VALUES(airc),\n" +
                    "area = VALUES(area),\n" +
                    "carOilColor = VALUES(carOilColor),\n" +
                    "enviroment = VALUES(enviroment),\n" +
                    "trim = VALUES(trim)\n" +
            "</script>",})
    void updateVinByid(@Param("dtos") List<FordV2VinAnalyserResultDTO> dto);

    @Select({"SELECT max(id) from ford_epc_parts_detail"})
    Integer queryMaxIdForPartsDetail();

    @Select({"SELECT id, partsCode, textCondition1, textCondition2, textCondition3, begin, end from ford_epc_parts_detail " ,
//            "WHERE id = 2",
            "WHERE id > #{start} and id<=#{end}"
    })
    List<PartDetailDTO> queryPartsDetailById(@Param("start") Integer start, @Param("end") Integer end);

//    @Insert({"<script>INSERT INTO ford_epc_parts_detail(id,dealPartsCode, dealTextCondition1, dealTextCondition2, dealTextCondition3, dealBegin, dealEnd) values",
//            "<foreach item='dto' collection='dtos' open ='(' separator='),(' close = ')'>",
//            " #{dto.id}, #{dto.dealPartsCode}, #{dto.dealTextCondition1}, #{dto.dealTextCondition2}, #{dto.dealTextCondition3}, #{dto.dealBegin}, #{dto.dealEnd}",
//            "</foreach>",
//            "on duplicate key update ",
//            "dealPartsCode=VALUES(dealPartsCode), dealTextCondition1=VALUES(dealTextCondition1), dealTextCondition2=VALUES(dealTextCondition2), dealTextCondition3=VALUES(dealTextCondition3), dealBegin=VALUES(dealBegin), dealEnd=VALUES(dealEnd)",
//            "dealTextCondition3=VALUES(dealTextCondition3)",
//            "</script>"})
    @Insert({"<script>INSERT INTO ford_epc_parts_detail(id, dealTextCondition3) values",
            "<foreach item='dto' collection='dtos' open ='(' separator='),(' close = ')'>",
            " #{dto.id}, #{dto.dealTextCondition3}",
            "</foreach>",
            "on duplicate key update ",
            "dealTextCondition3=VALUES(dealTextCondition3)",
            "</script>"})
    void updateDealPartsDetail(@Param("dtos") List<PartDetailDTO> dtos);


    @Select("SELECT  idx FROM `ford_epc_text`")
    List<String> queryAllIdx();
}
